package com.sjsu.CMPE226.etl.dataloader;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.BatchUpdateException;
import java.util.List;

import com.sjsu.CMPE226.JDBC.config.NormalisedConnection;
import com.sjsu.CMPE226.normalized.jdbc.data.Network;
import com.sjsu.CMPE226.normalized.jdbc.data.Observation;
import com.sjsu.CMPE226.normalized.jdbc.data.Provider;
import com.sjsu.CMPE226.normalized.jdbc.data.Station;

/**
 * 
 * @author Deepali Pawar
 * 
 */
public class NormalisedJDBCDataLoader {

	public int loadStationsBatch(List<Station> stationList) {

		Connection conn = null;
		Statement stmt = null;
		int[] updateCounts = null;
		ResultSet rs = null;
		try {
			long st = System.currentTimeMillis();

			conn = NormalisedConnection.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			conn.setAutoCommit(false);
			for (Station station : stationList) {
				String sql = "INSERT INTO stations_info(Station_Id,Secondary_Station_Id, Station_Name,  Status,  State,  Country,  Latitude,  Longitude,  Elevation,  Network_Id,"
						+ "Wims_Id,  Primary_Provider_Id,  Secondary_Provider_Id,  Tertiary_Provider_Id) VALUES('"
						+ escapeSingleQuote(station.getStationId())
						+ "','"
						+ station.getSecondaryStationId()
						+ "','"
						+ escapeSingleQuote(station.getStationName())
						+ "','"
						+ station.getStatus()
						+ "','"
						+ station.getState()
						+ "','"
						+ station.getCountry()
						+ "',"
						+ station.getLatitude()
						+ ","
						+ station.getLongitude()
						+ ","
						+ station.getElevation()
						+ ","
						+ station.getNetworkId()
						+ ","
						+ station.getWimsId()
						+ ","
						+ station.getPrimaryProviderId()
						+ ","
						+ station.getSecondaryProviderId()
						+ ","
						+ station.getTertiaryProviderId() + ")";
				System.out.println("sql=" + sql);
				stmt.addBatch(sql);
			}

			updateCounts = stmt.executeBatch();
			conn.commit();
			long et = System.currentTimeMillis();
			System.out
					.println("Time to load data through JDBC in normalised DB 'stations_info' Table:"
							+ (et - st) + "msec");
			rs = stmt.executeQuery("SELECT * FROM stations_info");
			while (rs.next()) {
				String id = rs.getString("Station_Id");
				String name = rs.getString("Secondary_Station_Id");
				System.out.println("id=" + id + "  name=" + name);
			}

		} catch (BatchUpdateException b) {
			System.err.println("SQLException: " + b.getMessage());
			System.err.println("SQLState: " + b.getSQLState());
			System.err.println("Message: " + b.getMessage());
			System.err.println("Vendor error code: " + b.getErrorCode());
			System.err.print("Update counts: ");
			updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.err.print(updateCounts[i] + " ");
			}
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
			System.err.println("SQLState: " + ex.getSQLState());
			System.err.println("Message: " + ex.getMessage());
			System.err.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (Exception ignore) {
			}
		}
		return updateCounts.length;
	}

	private String escapeSingleQuote(String s) {
		return s.replaceAll("'", "''");

	}

	public int loadProvidersBatch(List<Provider> providerList) {
		Connection conn = null;
		Statement stmt = null;
		int[] updateCounts = null;
		ResultSet rs = null;
		try {
			long st = System.currentTimeMillis();

			conn = NormalisedConnection.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			conn.setAutoCommit(false);
			for (Provider provider : providerList) {
				stmt.addBatch("INSERT INTO network_provider_info(Provider_Id,Provider_Name) VALUES("
						+ provider.getProviderId()
						+ ",'"
						+ provider.getProviderName() + "')");
			}

			updateCounts = stmt.executeBatch();
			conn.commit();
			long et = System.currentTimeMillis();
			System.out
					.println("Time to load data through JDBC in normalised DB in 'Provider' Table :"
							+ (et - st) + "msec");
			rs = stmt.executeQuery("SELECT * FROM network_provider_info");
			while (rs.next()) {
				String id = rs.getString("Provider_Id");
				String name = rs.getString("Provider_Name");
				System.out.println("id=" + id + "  name=" + name);
			}

		} catch (BatchUpdateException b) {
			System.err.println("SQLException: " + b.getMessage());
			System.err.println("SQLState: " + b.getSQLState());
			System.err.println("Message: " + b.getMessage());
			System.err.println("Vendor error code: " + b.getErrorCode());
			System.err.print("Update counts: ");
			updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.err.print(updateCounts[i] + " ");
			}
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
			System.err.println("SQLState: " + ex.getSQLState());
			System.err.println("Message: " + ex.getMessage());
			System.err.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (Exception ignore) {
			}
		}
		return updateCounts.length;
	}

	public int loadNetworksBatch(List<Network> networkList) {
		Connection conn = null;
		Statement stmt = null;
		int[] updateCounts = null;
		ResultSet rs = null;
		try {
			long st = System.currentTimeMillis();

			conn = NormalisedConnection.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			conn.setAutoCommit(false);
			for (Network network : networkList) {

				stmt.addBatch("INSERT INTO mesowest_network_info(Network_Id,Network_Name) VALUES("
						+ network.getNetworkId()
						+ ",'"
						+ network.getNetworkName() + "')");
			}

			updateCounts = stmt.executeBatch();
			conn.commit();
			long et = System.currentTimeMillis();
			System.out
					.println("Time to load data through JDBC in normalised DB in 'Network' Table:"
							+ (et - st) + "msec");
			rs = stmt.executeQuery("SELECT * FROM mesowest_network_info");
			while (rs.next()) {
				String id = rs.getString("Network_Id");
				String name = rs.getString("Network_Name");
				System.out.println("id=" + id + "  name=" + name);
			}

		} catch (BatchUpdateException b) {
			System.err.println("SQLException: " + b.getMessage());
			System.err.println("SQLState: " + b.getSQLState());
			System.err.println("Message: " + b.getMessage());
			System.err.println("Vendor error code: " + b.getErrorCode());
			System.err.print("Update counts: ");
			updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.err.print(updateCounts[i] + " ");
			}
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
			System.err.println("SQLState: " + ex.getSQLState());
			System.err.println("Message: " + ex.getMessage());
			System.err.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (Exception ignore) {
			}
		}
		return updateCounts.length;
	}

	public int loadObservationsBatch(List<Observation> observationsList) {
		Connection conn = null;
		Statement stmt = null;
		int[] updateCounts = null;
		ResultSet rs = null;
		try {
			long st = System.currentTimeMillis();

			conn = NormalisedConnection.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			conn.setAutoCommit(false);
			for (Observation observation : observationsList) {
				if (observation.getObservationsDateTime() == null) {
				} else {
					stmt.addBatch("INSERT INTO observations(Station_Id,ObservationsDateTime,Temperature,Wind_Speed,Direction,Gust,Sea_Level_Pressure,Altimeter,"
							+ "Dew_Point,Relative_Humidity,Numeric_Gempak_Weather_Code,P24I) VALUES('"
							+ observation.getStationId()
							+ "','"
							+ observation.getObservationsDateTime()
							+ "',"
							+ observation.getTemperature()
							+ ","
							+ observation.getWindSpeed()
							+ ","
							+ observation.getDirection()
							+ ","
							+ observation.getGust()
							+ ","
							+ observation.getSeaLevelPressure()
							+ ","
							+ observation.getAltimeter()
							+ ","
							+ observation.getDewPoint()
							+ ","
							+ observation.getRelativeHumidity()
							+ ","
							+ observation.getWeatherCode()
							+ ","
							+ observation.getP24I() + ")");
				}
			}

			updateCounts = stmt.executeBatch();
			conn.commit();
			long et = System.currentTimeMillis();
			System.out
					.println("Time to load data through JDBC in normalised DB in 'Observation' Table:"
							+ (et - st) + "msec");
			/*rs = stmt.executeQuery("SELECT * FROM observations");
			while (rs.next()) {
				String id = rs.getString("ObservationsDateTime");
				String name = rs.getString("Station_Id");
				System.out.println("id=" + id + "  name=" + name);
			}*/

		} catch (BatchUpdateException b) {
			System.err.println("SQLException: " + b.getMessage());
			System.err.println("SQLState: " + b.getSQLState());
			System.err.println("Message: " + b.getMessage());
			System.err.println("Vendor error code: " + b.getErrorCode());
			System.err.print("Update counts: ");
			updateCounts = b.getUpdateCounts();
			for (int i = 0; i < updateCounts.length; i++) {
				System.err.print(updateCounts[i] + " ");
			}
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
			System.err.println("SQLState: " + ex.getSQLState());
			System.err.println("Message: " + ex.getMessage());
			System.err.println("Vendor error code: " + ex.getErrorCode());
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (Exception ignore) {
			}
		}
		return updateCounts.length;
	}
}
